--------------------------------------------------------------------------------
-- @name: tpack.sql
-- @author: dion cho
-- @note: source for tpack package
-- @todo :

--------------------------------------------------------------------------------
--spool tpack.txt
set echo on
set verify on
set scan off

drop package tpack;






-- create package spec
create or replace package tpack authid definer is

  --------------------------------------
  -- constants
  --------------------------------------
  
  --------------------------------------
  -- exception
  --------------------------------------
  JOB_ID_IS_NULL_ERR_NO               number := -20001;
  JOB_ID_IS_NULL_ERR_MSG              varchar2(1000) := 
      'Job id is null. Execute create_job_id function to continue your job.';
  
  TRY_DECODE_IS_INVALID_ERR_NO        number := -20002;
  TRY_DECODE_IS_INVALID_ERR_MSG       varchar2(1000) := 
      'TRY_DECODE should be either 0 or 1';
  
  SNAPSHOT_NOT_STARTED_ERR_NO         number := -20003;
  SNAPSHOT_NOT_STARTED_ERR_MSG        varchar2(1000) :=
      'Execute begin_session_snapshot beforehand';
  
  DIAG_NOT_STARTED_ERR_NO             number := -20004;
  DIAG_NOT_STARTED_ERR_MSG            varchar2(1000) :=
      'Execute begin_diag_trace beforehand';
  
  INV_CONTENTION_TYPE_ERR_NO          number := -20005;
  INV_CONTENTION_TYPE_ERR_MSG         varchar2(1000) :=
      'Invalid contention type. It should be one of (latch, enqueue, library cache lock, library cache pin, row cache lock, mutex)';
  
  INV_JOB_ID_RANGE_ERR_NO             number := -20006;
  INV_JOB_ID_RANGE_ERR_MSG            varchar2(1000) :=
      'Invalid range of recente job ids';
  
  BOTH_SID_SQLID_NULL_ERR_NO          number := -20007;
  BOTH_SID_SQLID_NULL_ERR_MSG         varchar2(1000) :=
      'Both session id and sql id are null';
  
  INV_POOL_NAME_ERR_NO                number := -20008;
  INV_POOL_NAME_ERR_MSG               varchar2(1000) :=
      'Invalid pool name. Pool name should one of (all, shared pool, large pool, java pool, streams pool)';
  
  INV_SUBHEAP_DEPTH_ERR_NO            number := -20009;
  INV_SUBHEAP_DEPTH_ERR_MSG           varchar2(1000) := 
      'Invalid subheap recursive depth. It should be one of 0, 1 and 2.';
  
  DIRECTORY_NOT_EXISTS_ERR_NO         number := -20010;
  DIRECTORY_NOT_EXISTS_ERR_MSG        varchar2(1000) :=
      'Directory object is not existent for the given file name.';
      
  SYSTEM_CALL_NOT_SUPP_ERR_NO         number := -20011;
  SYSTEM_CALL_NOT_SUPP_ERR_MSG        varchar2(1000) :=
      'System call report is not supported in this sytem';
  
  COMMAND_NOT_EXISTS_ERR_NO           number := -20012;
  COMMAND_NOT_EXISTS_ERR_MSG          varchar2(1000) :=
      'Command does not exist';
      
      
  --------------------------------------
  -- session info 
  --------------------------------------
  g_sid         number;
  g_serial_no   number;
  g_pid         number;
  
  
  --------------------------------------
  -- type definition
  --------------------------------------
  type varchar2_array is table of varchar2(20000);
  type property_array is table of property;
  
  type session_array is table of sess_obj;
  type hot_block_array is table of hot_block;
  
  type session_snapshot_array is table of session_snapshot_type;
  
  type latch_prof_type_array is table of latch_prof_type;
  
  type wait_prof_type_array is table of wait_prof_type;
  
  type heap_usage_array is table of heap_usage_type;
  
  type sga_stat_array is table of sga_stat_type;
  
  type stack_prof_array is table of stack_prof_type;
  
  type func_desc_array is table of func_desc_type;
  
  type oerr_type_array is table of oerr_type;
  
  type active_sql_array is table of active_sql;
  type sql_type_array is table of sql_type;
  type bind_type_array is table of bind_type;
  
  
  -- generic type
  type number_array is table of number index by pls_integer;
  type number_array2 is table of number index by varchar2(1000);
  
  type boolean_array is table of boolean index by pls_integer;
  type varchar2_array2 is table of varchar2(20000) index by pls_integer;
  
  
  -- timing related types
  type timing_array is table of timing_type index by pls_integer;
  
  
  --------------------------------------
  -- tpack configuration
  --------------------------------------
  g_config    tpack_configuration%rowtype;
  g_debug     boolean := false;
  g_remove_temp_file    boolean := true;
  
  g_cur_job_id    varchar2(50);

  
  g_timing    boolean := false;
  g_timing_stack  timing_array;
  g_timing_idx  number := 1;
  
  -- recent job ids
  MAX_RECENT_JOBS       number := 10;
  g_recent_job_ids      varchar2_array2;
  
  ---------------------------------------
  -- session snapshot related variables
  ---------------------------------------
  g_cur_snap_arr  number_array;
  
  ---------------------------------------
  -- diag event related variables
  ---------------------------------------

  g_diag_trace_file_arr varchar2_array2;
  g_diag_start_pos_arr  number_array;
  
  ---------------------------------------
  -- configuration
  ---------------------------------------
  
  ------------------------------------------------------------------------------
  -- Eanble debugging mode
  ------------------------------------------------------------------------------
  procedure enable_debug;
  
  ------------------------------------------------------------------------------
  -- Disable debugging mode
  ------------------------------------------------------------------------------
  procedure disable_debug;
  
  ------------------------------------------------------------------------------
  -- Is debugging enabled?
  ------------------------------------------------------------------------------
  function debug_enabled return boolean;
  
  ------------------------------------------------------------------------------
  -- Record debugging message to the log file.
  -- The log file is stored at the temporary directory which you speficied while installing the tpack library.
  -- And the log file format is yyyymmdd.log
  ------------------------------------------------------------------------------
  procedure debug(msg in varchar2);
  
  ------------------------------------------------------------------------------
  -- Enable/Disable timing mode
  -- When enabled, the timing information would be written to the log file.
  -- Debugging mode should be enabled to use this feature (tpack.enable_debug)
  ------------------------------------------------------------------------------
  procedure set_timing(b_timing boolean);
  
  
  ------------------------------------------------------------------------------
  -- Reset timing info
  ------------------------------------------------------------------------------
  procedure reset_timing;
  
  
  ------------------------------------------------------------------------------
  -- remove temporary files?
  ------------------------------------------------------------------------------
  function remove_temp_file
      return boolean;
  
  ------------------------------------------------------------------------------
  -- enable/disable remove temporary file
  ------------------------------------------------------------------------------
  procedure enable_remove_temp_file(bEnable   boolean);
  
  
  ---------------------------------------
  -- tracefile manipulation
  ---------------------------------------
  
  ------------------------------------------------------------------------------
  -- Get current trace file name of the given session.
  -- @param session_id  number    session id. default = current session
  -- @return  the trace file name
  ------------------------------------------------------------------------------
  function get_tracefile_name(session_id in number default userenv('sid')) return varchar2;

  ------------------------------------------------------------------------------
  -- Get trace file contents of the given filename and starting positition(offset) within the file
  -- This function wraps the call to get_tracefile_contents_java/utl_file
  
  -- @param   tracefile_name    varchar2    The trace file name
  -- @param   start_pos         number      the starting position within the file  
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function get_tracefile_contents
    (tracefile_name varchar2, start_pos number default 0)
    return varchar2_array
    pipelined;


  ------------------------------------------------------------------------------
  -- Get trace file contents of the given filename and starting positition(offset) within the file using java stored procedre
  -- You don't need direct call to this function.
  -- @param   tracefile_name    varchar2    The trace file name
  -- @param   start_pos         number      the starting position within the file
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function get_tracefile_contents_java
    (tracefile_name varchar2, start_pos number default 0)
    return varchar2_array
    pipelined;

  ------------------------------------------------------------------------------
  -- Get trace file contents of the given filename and starting positition(offset) within the file using utl_file package.
  -- You don't need direct call to this function
  -- @param   tracefile_name    varchar2    The trace file name
  -- @param   start_pos         number      the starting position within the file  
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function get_tracefile_contents_file
    (tracefile_name varchar2, start_pos number default 0)
    return varchar2_array
    pipelined;


  ---------------------------------------
  -- helper function 
  ---------------------------------------
  function get_N(p_value in number)
    return number;
  
  -- helper function to display deltas
  function get_deltas(session_id in number, type_name in varchar2, item_name in varchar2)
    return varchar2;
    
  
  -- sleep for a while, always return 1;
  function sleep(placeholder number, sleep_time number)
  return number;
  
  
  -- sleep for a while - procedure version
  procedure sleep(sleep_time number);
  
  
  -- wait for another process signal me. procedure version
  procedure wait_for_signal(p_maxwait number default dbms_pipe.maxwait);
  
  -- wait for another process signal me. function version
  function f_wait_for_signal(p_maxwait number default dbms_pipe.maxwait) return number;
  
  -- signal another process
  procedure signal;
  
  -- convert hexadecial to decimal
  function to_dec(
          p_str in varchar2,
          p_from_base in number default 16)
    return number
  ;
  
  -- convert decimal to hexadecimal
  function to_hex(p_dec in number)
    return varchar2
  ;
  
  
  -- get OS name in lower case
  -- It might be one of linux, sunos, solaris, aix, hp-ux, win*, mac*
  function get_os return varchar2;
  
  
  ------------------------------------------------------------------------------
  -- load oraus.msg file
  -- @param     oraus_file_name   varchar2    The absolute path of the oraus.msg file
  ------------------------------------------------------------------------------
  procedure oerr_load(oraus_file_name in varchar2);
  
  
  ------------------------------------------------------------------------------
  -- Show oerr message. You need to execute tpack.oerr_load procedure to use this function.
  -- @param   err_code  number    The oerr code. for eg. 1555
  -- @return  array of the oerr_type object
  ------------------------------------------------------------------------------
  function oerr(err_code in number) 
    return oerr_type_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Search items in the oraus.msg file. 
  -- @param		search_value		varchar2	The string you want to search
  -- @return	array of the oerr_type object
  ------------------------------------------------------------------------------
  function oerr_search(search_value in varchar2)
    return oerr_type_array
    pipelined;
    
	
  ------------------------------------------------------------------------------
  -- Get initialization parameter information
  -- @param		search_value		varchar2	The string you want to search. It searches through name and description
  -- @return	array of the property object
  ------------------------------------------------------------------------------
  function param(search_value	in varchar2)
	return property_array
	pipelined;
	
	

  
  ---------------------------------------
  -- job control
  ---------------------------------------
  
  ------------------------------------------------------------------------------
  -- Create job id. you should create the job id to cancel it dynamically during the execution
  -- @param   is_server  number 1 = server, 0 = not server. default = 0
  -- @return  varchar2  The unqiue job id
  ------------------------------------------------------------------------------
  function create_job_id(is_server number default 0)
      return varchar2;
  
  
  ------------------------------------------------------------------------------
  -- Procedure version of create_job_id
  ------------------------------------------------------------------------------
  procedure create_job_id(is_server number default 0);
  
  
  ------------------------------------------------------------------------------
  -- Get recent job id list
  ------------------------------------------------------------------------------
  function recent_job_ids
    return varchar2_array
    pipelined;
    
  
  
  ------------------------------------------------------------------------------
  -- Get recent job id 
  -- @param     nth     number, 0 = last, -1 = last-1, -2 = last2-2, .... default = 0 which means the most rectent one
  ------------------------------------------------------------------------------
  function recent_job_id(nth in number default 0) 
    return varchar2;
    
  
  ------------------------------------------------------------------------------
  -- Get current job id. 
  -- @return  varchar2  The unqiue job id
  ------------------------------------------------------------------------------
  function get_job_id return varchar2;
  
  ------------------------------------------------------------------------------
  -- Cancel the job. The job id is the unique identifier returned by create_job_id
  -- @param job_id  varchar2    The unique job id returned by create_job_id function
  ------------------------------------------------------------------------------
  procedure cancel_job(job_id in varchar2);
  
  ---------------------------------------
  -- session basic info
  ---------------------------------------
  ------------------------------------------------------------------------------
  -- Get the basic information of the session
  -- @param  session_id  number    ID of the session to be investigated. default = crrent session
  -- @param   b_include_plan    number  Include execution plan? default = 0
  -- @param   plan_option       varchar2  default = typical
  -- @return  array of the property object
  ------------------------------------------------------------------------------
  function session_basic(
        session_id      in number default userenv('sid'),
        b_include_plan  in number default 0,
        plan_option     in varchar2 default 'typical')
    return property_array
    pipelined;
  
  ------------------------------------------------------------------------------
  -- Get the basic information of the wait event
  -- @param  session_id   number   ID of the session to be investigated. default = current session
  -- @return  array of the property object
  ------------------------------------------------------------------------------
  function wait_basic(session_id in number default userenv('sid'))
    return property_array
    pipelined;
  
  ------------------------------------------------------------------------------
  -- Get the intelligent and additional information of the wait event
  --- which includes "latch holder", "hot block", "enqueue holder", etc.
  -- @param  session_id   number   ID of the session to be investigated. default = current session
  -- @return array of the property object
  ------------------------------------------------------------------------------
  function wait_detail(session_id in number default userenv('sid'))
    return property_array
    pipelined;
  
  ------------------------------------------------------------------------------
  -- Get the blocks which are propected by the CBC latch which is hold by the session
  -- @param  session_id   number   ID of the session holding the CBC latch. default = current session
  -- @param  max_block_no  number  The maximum count of the block list. default = 10000000
  -- @return  array of the hot_block object
  ------------------------------------------------------------------------------
  function hot_block_report(
        session_id in number default userenv('sid'), 
        max_block_no in number default 10000000)
    return hot_block_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Get session info in one shot, which includes
  --     session_basic : basic info. always enabled.
  --     opt_env : session specific optimizer enviroment
  --     plan_typical  : runtime plan with typical option
  --     plan_advanced : runtime plan with advanced option
  --     wait_basic    : basic wait info
  --     wait_detail   : detail wait info(aka intelligent wait analysis)
  
  -- @param     session_id   number   ID of the session. efault = current session
  -- @param     options      varchar2 The options of the session detail. ex) session_basic+wait_detail. default = session_basic
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0     
  -- @return  array of the property object
  ------------------------------------------------------------------------------
  function session_detail(
        session_id      in number default userenv('sid'),
        options         in varchar2 default 'session_basic',
        store_to_table  in number default 1)
    return property_array
    pipelined;
    
  
  ------------------------------------------------------------------------------
  -- Procedure version of session_detail
  ------------------------------------------------------------------------------
  procedure session_detail(
        session_id      in number default userenv('sid'),
        options         in varchar2 default 'session_basic')
  ;
  
  ------------------------------------------------------------------------------
  -- History version of session detail
  -- @param     job_id     The job id
  -- @return  array of the property object
  ------------------------------------------------------------------------------
  function session_history_detail(
        jobid     in varchar2 default tpack.get_job_id)
    return property_array
    pipelined;
    
  
  
  
  ------------------------------------------------------------------------------
  -- Get SQL detail information, which includes
  --    sql_basic : sql meta + sql text. always enabled
  ---   sql_plan : runtime plan of the query
  --    AWR_plan : plan that is stored onto AWR
  --    child_cursors : v$sql_shared_cursor 
  --    bind_capture : bind value from bind_capture
  --    opt_env : environment values(v$sql_optimizer_env)
  --    followings are advanced options
  --      plan_statistics : plan statistics (only available when the plan statistics was collected) - note: sql execution should be done before calling this method
  --      cursor_dump : cursor dump from error stack3,, which includes real bind value(this is very heavy!!) - note: the session should be currently executing the SQL statement
  --      histogram : histogram info for the dependent tables
  --      object_describe: : object meta data for the dependent tables and indexes(DDL)

  -- @param   session_id      number      ID of the session. With this value given, the sql_id and child_number are ignored.
  -- @param   sql_id          varchar2    ID of the sql. We retrieve the sql id from the session when this value is null.
  -- @param   child_number    number      child number
  -- @param   options         varchar2    The options of sql detail. ex) sql_basic+cursor_dump_histogram. default = sql_basic
  -- @param   store_to_table  number      Store the result to the table? 1 = yes, 0 = no. default = 0   
  -- @return  array of the property object 
  ------------------------------------------------------------------------------
  function sql_detail(
        session_id        in number default null,
        sql_id            in varchar2 default null,
        child_number      in number default 0, 
        options           in varchar2 default 'sql_basic',
        store_to_table    in number default 1)
    return property_array
    pipelined;
  
  
  
  ------------------------------------------------------------------------------
  -- Procedure version of sql_detail
  ------------------------------------------------------------------------------
  procedure sql_detail(
        session_id        in number,
        sql_id            in varchar2 default null,
        child_number      in number default 0, 
        options           in varchar2 default 'all');
        
        
  
  ------------------------------------------------------------------------------
  -- history version of sql_detail
  ------------------------------------------------------------------------------
  function sql_history_detail(jobid in varchar2 default tpack.get_job_id)
    return property_array
    pipelined;
    
  
  ------------------------------------------------------------------------------
  -- Get session list
  -- @param  b_include_bg   number    include background session? default = 0
  -- @return  array of the sess_obj object
  ------------------------------------------------------------------------------
  function session_list(
        b_include_bg in number default 0)
    return session_array
    pipelined;
  
  ------------------------------------------------------------------------------
  -- Get block dump
  -- @param  file_no   number     file number
  -- @param  block_no  number     block number
  -- @param  try_decode number    1 = decode, 0 = do not decode
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function block_dump(file_no in number, block_no in number, try_decode in number default 1) 
    return varchar2_array
    pipelined;
    
  
  ------------------------------------------------------------------------------
  -- begin session snapshot
  -- @param   session_id    number    ID of the session to be snapshoted. default = current session id. When the session is less than 0, it collects data from V$SYSSTAT not V$SESSTAT
  -- @param   include_sys_level number  Include system level data(latch, row cache, mutex, buf get)? 1=yes, 0=no, default = 0
  ------------------------------------------------------------------------------
  procedure begin_session_snapshot(
        session_id in number default userenv('sid'),
        include_sys_level in number default 1);
  
  ------------------------------------------------------------------------------
  -- add session snapshot
  -- @param   session_id    number    ID of the session to be snapshoted. default = current session id. When the session is less than 0, it collects data from V$SYSSTAT not V$SESSTAT
  -- @param   include_sys_level number  Include system level data(latch, row cache, mutex, buf get)? 1=yes, 0=no, default = 0
  ------------------------------------------------------------------------------
  procedure add_session_snapshot(
        session_id in number default userenv('sid'),
        include_sys_level in number default 1);
  
  
  ------------------------------------------------------------------------------
  -- Get snapshot report
  -- @param  session_id    number    ID of the session to be snapshoted. default = current session id. When the session is less than 0, it collects data from V$SYSSTAT not V$SESSTAT
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0   
  -- @return  array of the session_snapshot_type objet
  ------------------------------------------------------------------------------
  function session_snapshot_report(
        session_id in number default userenv('sid'),
        store_to_table  in number default 1)
      return session_snapshot_array
      pipelined;
      
  
  
  ------------------------------------------------------------------------------
  -- Automize the snapshot report in one shot
  -- @param   session_id    number    ID of the session to be reported. When the session is less than 0, it collects data from V$SYSSTAT not V$SESSTAT
  -- @param   snapshot_count  number   how many snapshots 
  -- @param   sleep_time    number    how long(in seconds) to sleep for each snapshot
  -- @include_sys_level in number default 0
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0   
  -- @return  array of the session_snapshot_type object
  ------------------------------------------------------------------------------
  function session_snapshot_report(
        session_id in number, 
        snapshot_count in number,
        sleep_time in number,
        include_sys_level in number default 1,
        store_to_table  in number default 1)
      return session_snapshot_array
      pipelined;
      
  
  ------------------------------------------------------------------------------
  -- Procedure version of session_snapshot_report
  ------------------------------------------------------------------------------
  procedure session_snapshot_report(
        session_id in number, 
        snapshot_count in number,
        sleep_time in number,
        include_sys_level in number default 1)
  ;
  
  
  ------------------------------------------------------------------------------
  -- Get historical session snapshot report
  -- @param  jobid    varchar2    The job id. default = current job id
  -- @return array of the session_snapshot_type object
  ------------------------------------------------------------------------------
  function session_snapshot_hist_report(
        jobid       in varchar2 default tpack.get_job_id)
      return session_snapshot_array
      pipelined;
      
  
  
  ------------------------------------------------------------------------------
  -- Get the latch profile report by latch name + latch address + function name + object info + sql id
  -- @param   session_id   number    The id of the session to be profiled
  -- @param   profile_data  varchar2  which data is profiled. The combination of LATCH_NAME, LATCH_ADDR, CODE_NAME, OBJ_WHY, SQL_ID. default = LATCH_NAME, CODE_NAME
  -- @param   latch_filter  varchar2    null = all latches, or latch name
  -- @param   prof_duration in number   How long(in seconds) to be profiled?
  -- @param   sleep_time     in number  How long(in seconds) to sleep for each profile. default = 0
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0  
  -- @return  array of the latch_prof_type object
  ------------------------------------------------------------------------------
  function latch_profile_report(
        session_id in number, -- sid to profile
        profile_data in varchar2 default 'LATCH_NAME, CODE_NAME', 
        latch_filter in varchar2 default null, -- latch name or all latch(null)
        prof_duration in number default 5,
        sleep_time    in number default 0, 
        store_to_table  in varchar2 default 1) 
    return latch_prof_type_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Procedure version of the latch_profile_report function.
  -- Primarily used by background collection server
  ------------------------------------------------------------------------------
  procedure latch_profile_report(
        session_id in number, -- sid to profile
        --target in varchar2 default 'CURRENT', -- for holder or current session
        profile_data in varchar2 default 'LATCH_NAME, CODE_NAME', 
        latch_filter in varchar2 default null, -- latch name or all latch(null)
        prof_duration in number default 5,
        sleep_time    in number default 0);
        
  ------------------------------------------------------------------------------
  -- Get the latch profile report of the given job id
  -- @param   jobid   varchar2    The job id
  -- @return  array of the latch_prof_type object
  ------------------------------------------------------------------------------
  function latch_profile_history_report(
        jobid       in varchar2 default tpack.get_job_id) 
    return latch_prof_type_array
    pipelined;
    
  
  
  ------------------------------------------------------------------------------
  -- Get the wait profile report for the give session.
  -- @param		session_id	number		The ID of the session to be profiled. default = current session
  -- @param		event_name	varchar2	The wait event name to be profiled. default = null which means all events
  -- @param		params   varchar2	The parameters to be profiled. default = null which means no parameter. for ex) "p1", "p1, p2"
  -- @param		prof_duration number	How long to prifle? default = 5 (sec)
  -- @param   sleep_time     in number  How long(in seconds) to sleep for each profile. default = 0
  -- @param		store_to_table	varchar2	 Store the result to the table. 1 = yes, 0 = no. default = 0 
  -- @return 	array of the wait_prof_type object
  ------------------------------------------------------------------------------
  function wait_profile_report(
			session_id	in number default userenv('sid'),
			event_name	in varchar2 default null,
			params		in varchar2 default null,
			prof_duration	in number default 5,
			sleep_time    in number default 0, 
			store_to_table	in varchar2 default '1')
	return wait_prof_type_array
	pipelined;
	

  ------------------------------------------------------------------------------
  -- procedure version of wait_profile_report
  ------------------------------------------------------------------------------
  procedure wait_profile_report(
			session_id	in number default userenv('sid'),
			event_name	in varchar2 default null,
			params		in varchar2 default null,
			prof_duration	in number default 5,
			sleep_time    in number default 0);
			
			
	
  ------------------------------------------------------------------------------
  -- Get the wait profile report for the given 10046 enabled(with level>=8) trace file
  -- @param		tracefile_name	varchar2	The 10046(level >= 8) trace file name
  -- @param		event_name	varchar2	The wait event name to be profiled. default = null which means all events
  -- @param		params   varchar2	The parameters to be profiled. default = null which means no parameter. for ex) "p1", "p1, p2", "p1, p2, p3", "p1, p2, p3, obj"
  --								Note the existence of the "obj" parameter, which means the object id of the wait target.
  -- @param		store_to_table	varchar2	 Store the result to the table. 1 = yes, 0 = no. default = 0 
  -- @return 	array of the wait_prof_type object
  ------------------------------------------------------------------------------
  function wait_file_report(
			tracefile_name		in varchar2 default get_tracefile_name,
			event_name	in varchar2 default null,
			params		in varchar2 default null,
			store_to_table	in varchar2 default '1')
	return wait_prof_type_array
	pipelined;

    
  ------------------------------------------------------------------------------
  -- Get the wait profile report from historical data
  -- @param   jobid   varchar2    The job id
  -- @return  arary of the wait_prof_type object
  ------------------------------------------------------------------------------
  function wait_profile_history_report(
        jobid       in varchar2 default tpack.get_job_id) 
    return wait_prof_type_array
    pipelined;

	
  ------------------------------------------------------------------------------
  -- Begin diangostic trace
  -- @param   session_id      number     The ID of the session to be traced. default = current session
  -- @param   event_no        number     Diagnostics event no. default = 10046
  -- @param   event_level     number     Diagnostics event level. default = 1
  ------------------------------------------------------------------------------
  procedure begin_diag_trace(
          session_id  in number default userenv('sid'),
          event_no    in number default 10046,
          event_level in number default 1);
          
  
  
  ------------------------------------------------------------------------------
  -- End diangostic trace
  -- @param   session_id      number     The ID of the session to be traced. default = current session
  -- @param   event_no        number     Diagnostics event no. default = 10046
  -----------------------------------------------------------------------------
  procedure end_diag_trace(
          session_id  in number default userenv('sid'),
          event_no    in number default 10046);
  
  
  ------------------------------------------------------------------------------
  -- Get the diagnostic trace data
  -- @param   session_id      number     The ID of the session to be traced. default = current session
  -- @param   result          varchar2   For 10046, is tkprof to be done? TRACE, TKPROF. default = TRACE
  -- @param	  tkprof_option	  varchar2	 The tkprof option in case of result = TKPROF. default = NULL, ex) sys=no
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0   
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function get_diag_trace(
          session_id  in number default userenv('sid'),
          result      in varchar2 default 'TRACE',
		  tkprof_option	in varchar2 default NULL,
          store_to_table  in number default 1)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Get the diagnostic trace data in one shot
  -- @param   session_id      number     The ID of the session to be traced
  -- @param   event_no        number     The diagnostic event. 10046, 10053, ...
  -- @param   event_level     number     The level. 1, 2, .... 
  -- @param   trace_count     number     how many times to get a trace? default = 1
  -- @param   sleep_time      number     How long to sleep for each trace? default = 10, which means the trace of the 10 sec interval
  -- @param   result          varchar2   For 10046, is tkprof to be done? BOTH, TRACE, TKPROF. default = TKPROF
  -- @param	  tkprof_option	  varchar2	 The tkprof option in case of result = TKPROF. default = NULL, ex) sys=no
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0     
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function get_diag_trace_oneshot (
          session_id  in number default userenv('sid'),
          event_no    in number default 10046,
          event_level in number default 1,
          trace_count   in number default 1,
          sleep_time   in number default 10,
          result      in varchar2 default 'TRACE',
		  tkprof_option	in varchar2 default NULL,
          store_to_table  in number default 1)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Procedure version of get_diag_trace 
  ------------------------------------------------------------------------------
  procedure get_diag_trace(
          session_id  in number default userenv('sid'),
          result      in varchar2 default 'TRACE',
		  tkprof_option	in varchar2 default NULL);
          
          
  
  ------------------------------------------------------------------------------
  -- Procedure version of get_diag_trace (in one shot)
  ------------------------------------------------------------------------------
  procedure get_diag_trace_oneshot (
          session_id  in number default userenv('sid'),
          event_no    in number default 10046,
          event_level in number default 1,
          trace_count   in number default 1,
          sleep_time   in number default 10,
          result      in varchar2 default 'TRACE',
 		  tkprof_option	in varchar2 default NULL);  
		  
  
  ------------------------------------------------------------------------------
  -- Get historical diag event report
  -- @param   jobid       varchar2    The job id
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function get_history_diag_trace(
        jobid     in varchar2 default tpack.get_job_id)
    return varchar2_array
    pipelined;
  
  
  
  ------------------------------------------------------------------------------
  -- Get the contents of the dump file
  -- @param   session_id         number    The ID of the session to be dumped
  -- @param   dump_name   varchar2  The dump name. ex) processstate
  -- @param   dump_level  number    The dump level. ex) 10
  -- @param	  get_whole_contents	number Store the whole contensts? 1 = yes, 0 = no, get only the trace file name. default = 1
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0  
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function get_dump(
        session_id      in number,
        dump_name       in varchar2,
        dump_level      in number,
		get_whole_contents	in number default 1,
        store_to_table  in number default 1)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- procedure version of get_dump
  ------------------------------------------------------------------------------
  procedure get_dump(
        session_id      in number,
        dump_name       in varchar2,
        dump_level      in number,
		get_whole_contents	in number default 1);
        
        
  ------------------------------------------------------------------------------
  -- Get the dump contents from the stored result
  -- @param   jobid         varchar2    The job id
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function get_history_dump(
        jobid          in varchar2 default tpack.get_job_id)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Get process state dump
  -- @param   session_id        number    The ID of the session to be dumped. default = current session
  -- @param   dump_level        number    The dump level. default = 10
  -- @param   store_to_table    number  Store the result to the table. 1 = yes, 0 = no. default = 0    
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function process_dump(
        session_id      in number default userenv('sid'),
        dump_level      in number default 10,
		get_whole_contents	in number default 1,
	    store_to_table  in number default 1)
    return varchar2_array
    pipelined;
    
  
  ------------------------------------------------------------------------------
  -- procedure version of process_dump
  ------------------------------------------------------------------------------
  procedure process_dump(
        session_id      in number default userenv('sid'),
        dump_level      in number default 10,
		get_whole_contents	in number default 1);
        
        
  ------------------------------------------------------------------------------
  -- Get system state dump
  -- @param   dump_level  number    The dump level. default = 10 which includes short onfrom 10g. +256 would print short stack info
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0    
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function system_dump(
        dump_level          in number default 10,
		get_whole_contents	in number default 1,
        store_to_table      in number default 1)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- procedure version of system_dump
  ------------------------------------------------------------------------------
  procedure system_dump(
        dump_level          in number default 10,
		get_whole_contents	in number default 1);
        
  
  ------------------------------------------------------------------------------
  -- Get error stack dump
  -- @param   session_id        number    The ID of the session to be dumped. default = current session
  -- @param   dump_level        number    The dump level. default = 3
  -- @param   store_to_table    number  Store the result to the table. 1 = yes, 0 = no. default = 0    
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function error_dump(
        session_id      in number default userenv('sid'), 
        dump_level      in number default 3,
		get_whole_contents	in number default 1,
        store_to_table  in number default 1)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- procedure version of error_dump
  ------------------------------------------------------------------------------
  procedure error_dump(
        session_id      in number default userenv('sid'), 
        dump_level      in number default 3,
		get_whole_contents	in number default 1);
        
  
  ------------------------------------------------------------------------------
  -- Get callstack dump
  -- @param   session_id        number    The ID of the session to be dumped. default = current session
  -- @param   dump_level        number    The dump level. default = 1
  -- @param   store_to_table    number  Store the result to the table. 1 = yes, 0 = no. default = 0    
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function callstack_dump(
        session_id      in number default userenv('sid'), 
        dump_level      in number default 1,
		get_whole_contents	in number default 1,
        store_to_table  in number default 1)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- procedure version of callstack_dump
  ------------------------------------------------------------------------------
  procedure callstack_dump(
        session_id      in number default userenv('sid'), 
        dump_level      in number default 1,
		get_whole_contents	in number default 1);
      
      
  ------------------------------------------------------------------------------
  -- Get hang analyze
  -- @param   dump_level  number    The dump level. default = 10
--          10 - Dump all processes (IGN state)
--          5 - Level 4 + Dump all processes involved in wait chains (NLEAF state)
--          4 - Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
--          3 - Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
--          1-2 - Only HANGANALYZE output, no process dump at all
  -- @param   is_cluster      number  In case of RAC, dump cluster-wise hang analyze? 1 = yes, 0 = no. default = 0
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0    
  -- @return  array of the varchar2
  ------------------------------------------------------------------------------
  function hang_analyze(
        dump_level      in number default 10,
        is_cluster      in number default 0,
		get_whole_contents	in number default 1,
        store_to_table  in number default 1)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- procedure version of hang_analyze
  ------------------------------------------------------------------------------
  procedure hang_analyze(
        dump_level      in number default 10,
        is_cluster      in number default 0,
		get_whole_contents	in number default 1);
        
  
  
  ------------------------------------------------------------------------------
  -- Get heap dump. see heap_report for detail
  ------------------------------------------------------------------------------
  function heap_dump(
		session_id      in number default userenv('sid'), 
        dump_level      in varchar2 default '1',
		get_whole_contents	in number default 1,
        store_to_table  in number default 1)
	return varchar2_array
	pipelined;
	
  ------------------------------------------------------------------------------
  -- prcocedure version of heap dump
  ------------------------------------------------------------------------------
  procedure heap_dump(
		session_id      in number default userenv('sid'), 
        dump_level      in varchar2 default '1',
		get_whole_contents	in number default 1);
		
		
  ------------------------------------------------------------------------------
  -- get index tree dump
  ------------------------------------------------------------------------------
  function index_tree_dump(
        schema_name       in  varchar2,
        index_name        in  varchar2,
        partition_name    in  varchar2 default null)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Get stack trace profile report
  -- @param session_id    number  The ID of the session to be analyzed. default = current sid
  -- @param duration      number  The duration(s) of the whole profiling. default = 1
  -- @param sleep_time    number  The sleep time during each profiling. default = 0.1
  -- @param max_call_depth  number  The maximum call depth to display. default = 300
  -- @param delimitor     varchar2  The delimitor to be used in display. default = ->
  -- @param tree_like_format  number  1 = tree like, 0 = flat
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0  
  -- @return array of the stack_prof_type
  ------------------------------------------------------------------------------
  function callstack_prof_report (
        session_id  in number default userenv('sid'),
        duration  in number default 1,
        sleep_time  in number default 0.1,
        max_call_depth in number default 300,
        delimitor in varchar2 default '->',
        tree_like_format in number default 1,
        store_to_table   in number default 1)
    return stack_prof_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Procedure version of callstack_prof_report
  ------------------------------------------------------------------------------
  procedure callstack_prof_report (
        session_id  in number default userenv('sid'),
        duration  in number default 1,
        sleep_time  in number default 0.1,
        max_call_depth in number default 300,
        delimitor in varchar2 default '->',
        tree_like_format in number default 1)
  ;
  
  
  ------------------------------------------------------------------------------
  -- Get stack trace profile report directly from the trace file
  -- @param trace_file    varchar2 The trace file name of the callstack dump
  -- @param start_pos     number    The starting position to be read
  -- @param max_call_depth  number  The maximum call depth to display. default = 300
  -- @param delimitor     varchar2  The delimitor to be used in display. default = ->
  -- @param tree_like_format  number  1 = tree like, 0 = flat
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0  
  -- @return array of the stack_prof_type
  ------------------------------------------------------------------------------
  function callstack_file_report (
        trace_file  in varchar2,
        start_pos   in number default 0,
        max_call_depth in number default 300,
        delimitor in varchar2 default '->',
        tree_like_format in number default 1,
        store_to_table   in number default 1)
    return stack_prof_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Get historical stack trace profile report
  -- @param jobid     varchar2  The job id. default = current job id
  -- @return array of the stack_prof_type object
  ------------------------------------------------------------------------------
  function callstack_history_report (
        jobid     in varchar2 default tpack.get_job_id)
    return stack_prof_array
    pipelined;
  

  ------------------------------------------------------------------------------
  -- Get description for the function name in the callstack profile report
  -- @param   function_name   varchar2
  -- @return  array of the func_desc_type object
  ------------------------------------------------------------------------------
  function func_desc(function_name in varchar2) 
    return func_desc_array
    pipelined;
    


  ------------------------------------------------------------------------------
  -- Get system call report.
  --  On solaris/aix  : truss -adD (installed in the main distribution)
  --  On Linux        : strace -ttT (installed in the main distribution)
  --  On HP/UX        : tusc -aT (not installed in the main distribution)
  --  others          : not supported
  
  -- @param session_id    number  The ID of the session to be analyzed. default = current sid
  -- @param duration      number  The duration(s) of the whole profiling. default = 1
  -- @param format        varchar2  The format of the report. DETAIL or SUMMARY. default = DETAIL
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0  
  -- @return array of the varchar2
  ------------------------------------------------------------------------------
  function system_call_report (
        session_id  in number default userenv('sid'),
        duration  in number default 1,
        format    in varchar2 default 'DETAIL',
        store_to_table   in number default 1)
    return varchar2_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Procedure version of system_call_report
  ------------------------------------------------------------------------------
  procedure system_call_report (
        session_id  in number default userenv('sid'),
        duration  in number default 1,
        format    in varchar2 default 'DETAIL')
  ;
  
  
  
  ------------------------------------------------------------------------------
  -- Get historical system call report
  -- @param jobid     varchar2  The job id. default = current job id
  -- @return array of the analysis report
  ------------------------------------------------------------------------------
  function system_call_history_report (
        jobid     in varchar2 default tpack.get_job_id)
    return varchar2_array
    pipelined;
  
  
    
    
  ------------------------------------------------------------------------------
  -- Get heap(PGA/SGA) analysis report
  -- @param   session_id       number    The ID of the session to be analyzed. default = current sid
  -- @param   dump_level  varchar2  level of the heap dump. default = '0x20000001' which dumps PGA including the five larget subheaps recursively(>=10.2.0.1)
  --              1 = PGA
  --              2 = SGA
  --              3 = UGA
  --              8 = Current Call
  --              16 = User Call
  --              32 = Large Pool
  --              + 0x10000000 = including 5 largest subheap
  --              + 0x20000000 = including 5 largest subheap recursively.
  --              For eg, 0x20000001 = dump PGA heap including 5 largest subheap recursively
  
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0
  -- @return  array of the heap_usage_type object
  ------------------------------------------------------------------------------
  function heap_report(
          session_id in number default userenv('sid'), 
          dump_level in varchar2 default '0x20000001',
          store_to_table  in number default 1) 
    return heap_usage_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Procedure version of heap_report
  ------------------------------------------------------------------------------
  procedure heap_report(
          session_id in number default userenv('sid'), 
          dump_level in varchar2 default '0x20000001')
  ;
  
  ------------------------------------------------------------------------------
  -- Get SGA heap analysis report.
  -- @param   subheap_depth   number  recursive dump for subheap? 0 = top only, 1 = including 5 largest subheap, 2 = incluing 5 largest subheap recursively. default = 0
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0
  -- @return  array of the heap_usage_type object
  ------------------------------------------------------------------------------
  function sga_heap_report(
            subheap_depth   in number default 0,
            store_to_table  in number default 1) 
    return heap_usage_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Procedure version of sga_heap_report
  ------------------------------------------------------------------------------
  procedure sga_heap_report(
          subheap_depth   in number default 0)
  ;
            
  
  ------------------------------------------------------------------------------
  -- Get PGA heap analysis report
  -- @param   session_id       number    The ID of the session to be analyzed. default = current sid  
  -- @param   subheap_depth   number  recursive dump for subheap? 0 = top only, 1 = including 5 largest subheap, 2 = incluing 5 largest subheap recursively. default = 0
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0
  -- @return  array of the heap_usage_type object
  ------------------------------------------------------------------------------
  function pga_heap_report(
            session_id      in number default userenv('sid'),
            subheap_depth   in number default 0,
            store_to_table  in number default 1) 
    return heap_usage_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Procedure version of pga_heap_report
  ------------------------------------------------------------------------------
  procedure pga_heap_report(
          session_id      in number default userenv('sid'),
          subheap_depth   in number default 0)
  ;
            
            
  ------------------------------------------------------------------------------
  -- Get subheap analysis report
  -- @param   session_id       number    The ID of the session to be analyzed. default = current sid
  -- @param   subheap_addr  varchar2  The address of subheap
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0
  -- @return  array of the heap_usage_type object
  ------------------------------------------------------------------------------
  function subheap_report(
          session_id in number default userenv('sid'), 
          subheap_addr in varchar2,
          store_to_table  in number default 1)  
    return heap_usage_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Get heap analysis report directly from the existing trace file
  -- @param   tracefile_name    varchar2  the name of the trace file
  -- @param   start_pos         number    the starting position of the contents in the file. default = 0
  -- @param   store_to_table  number  Store the result to the table. 1 = yes, 0 = no. default = 0
  -- @return  array of the heap_usage_type object
  ------------------------------------------------------------------------------
  function heap_file_report (
          tracefile_name  in varchar2,
          start_pos in number default 0,
          store_to_table  in number default 1) 
    return heap_usage_array
    pipelined;
  
  
  ------------------------------------------------------------------------------
  -- Get heap analysis report from stored result
  -- @param   job_id    The job id to retrieve. default = current job id
  -- @return  array of the heap_usage_type object
  ------------------------------------------------------------------------------
  function heap_history_report(
          jobid     in varchar2 default tpack.get_job_id)
    return heap_usage_array
    pipelined;
  


  ------------------------------------------------------------------------------
  -- Get extented SGA stat report
  -- @param   pool    varchar2    which pool? all=all pools, shared pool, large pool, java pool, streams pool, default = shared pool
  -- @return  array of the sga_stat_type object
  ------------------------------------------------------------------------------
  function sga_stat(
        pool           in varchar2 default 'shared pool',
        store_to_table in number default 1)
    return sga_stat_array
    pipelined
  ;
  
  
  ------------------------------------------------------------------------------
  -- Get extented SGA stat report from 4031 diag trace
  -- @param   trace_file    the full path of the trace file containing 4031 diag trace info
  -- @return  array of the sga_stat_type object
  ------------------------------------------------------------------------------
  function sga_stat_4031(
        trace_file     in varchar2)
    return sga_stat_array
    pipelined
  ;
  

  ------------------------------------------------------------------------------
  -- Procedure version of sga_stat
  ------------------------------------------------------------------------------
  procedure sga_stat(
        pool           in varchar2 default 'shared pool');
  
  ------------------------------------------------------------------------------
  -- history version of sga_stat
  ------------------------------------------------------------------------------
  function sga_history_stat(
          jobid     in varchar2 default tpack.get_job_id)
    return sga_stat_array
    pipelined
  ;
  
  ------------------------------------------------------------------------------
  -- Delete historical reports of the given job id
  -- @param jobid     The job id. default = current job id. Delete all reports when job id is null.
  ------------------------------------------------------------------------------
  procedure delete_history_report(
        jobid   in varchar2 default tpack.get_job_id)
  ;

  ------------------------------------------------------------------------------
  -- purge all history reports.
  -- @param jobid     The job id. default = current job id. Delete all reports when job id is null.
  ------------------------------------------------------------------------------
  procedure purge_history_reports;


      
end tpack;
/

show errors

set scan on
--spool off
--ed tpack.txt

